package com.pkcargo.email;

import org.springside.modules.test.junit38.SpringContextTestCase;
import org.springframework.beans.factory.annotation.Autowired;
import org.hibernate.exception.ConstraintViolationException;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import com.pkcargo.service.user.EmailManager;
import com.pkcargo.entity.user.Email;

/**
 * User: Howard.Lin - linhua3721@gmail.com
 * DateTime: 2009-6-9 14:46:25
 * This is Pkcargo.com project.提供全球物流信息的抓取和垂直搜索
 */
public class LoadEmailToDb  extends SpringContextTestCase {
     @Autowired
    private EmailManager emailManager;
    //只作为数据导入使用，不是测试中的范例
    private void testEmailLoad(){
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url = "jdbc:sqlserver://192.168.3.4:1433;user=test001;password=test001;";
            Connection con = DriverManager.getConnection(url);
            Statement stmt = con.createStatement();
            ResultSet rs;
            Email email ;
            for (int i = 291; i < 292; i++) {
                String SQL = "SELECT id,userid,username,email " +
                        "FROM " +
                        "(select * ,row_number() over(order by id) as rownum from base) " +
                        "as T where rownum between "+ (i*1000+1) +" and "+ (i+1)*1000;
                rs = stmt.executeQuery(SQL);
                while (rs.next()) {
                    email = new Email();
                    email.setEmail(rs.getString("email"));
                    email.setUserid(rs.getString("userid"));
                    email.setUsername(rs.getString("username"));
                    try{
                        emailManager.save(email);
                    }
                    catch(ConstraintViolationException e){
                         System.out.println("email重复");
                    }
                    System.out.println(i+1 +"页," +rs.getString("id") + ", " + rs.getString("email"));
                }
                rs.close();
            }
            stmt.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

}
